/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package neo.workforce.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import neo.workforce.dao.Lookup;
import neo.workforce.dao.LookupDAO;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 *
 * @author KTB_User
 */
public class LookupDAOImpl implements LookupDAO {

    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public Lookup find(long id) {
        String sql = "SELECT ID, KEY_NAME, KEY_VALUE, KEY_GROUP FROM LOOKUP WHERE ID = ?";
        Lookup lookup = (Lookup) jdbcTemplate.queryForObject(
                sql, new Object[]{id}, new LookupRowMapper());
        return lookup;
    }

    public Long save(final Lookup lookup) {
        final String sql = "INSERT INTO LOOKUP (KEY_NAME, KEY_VALUE, KEY_GROUP, KEY_ORDER) VALUES(?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(
                new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps =
                        connection.prepareStatement(sql);
                ps.setString(1, lookup.getKeyName());
                ps.setString(2, lookup.getKeyValue());
                ps.setString(3, lookup.getKeyGroup());
                ps.setInt(4, lookup.getKeyOrder());
                return ps;
            }
        }, keyHolder);
        return new Long(keyHolder.getKey().longValue());
    }

    public boolean update(final Lookup lookup) {
        final String sql = "UPDATE LOOKUP SET KEY_NAME = ?, KEY_VALUE = ?, KEY_GROUP = ?, KEY_ORDER = ? WHERE ID = ?";
        try {
            jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql);
                    ps.setString(1, lookup.getKeyName());
                    ps.setString(2, lookup.getKeyValue());
                    ps.setString(3, lookup.getKeyGroup());
                    ps.setInt(4, lookup.getKeyOrder());
                    ps.setLong(5, lookup.getId());
                    return ps;
                }
            });
            return true;
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
    }

    public boolean remove(final long id) {
        final String sql = "DELETE FROM LOOKUP WHERE ID = ?";
        try {
            jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql);
                    ps.setLong(1, id);
                    return ps;
                }
            });
            return true;
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
    }

    public List findAll() {
        String sql = "SELECT ID, KEY_NAME, KEY_VALUE, KEY_GROUP FROM LOOKUP";
        List rows = jdbcTemplate.queryForList(sql, new LookupRowMapper(), new Object[]{});
        return rows;

    }

    public List findByGroup(String keyGroup) {
        String sql = "SELECT ID, KEY_NAME, KEY_VALUE, KEY_GROUP FROM LOOKUP WHERE KEY_GROUP = ?";
        List rows = jdbcTemplate.queryForList(sql, new LookupRowMapper(), new Object[]{keyGroup});
        return rows;
    }

    public List findByGroup(int begin, int limit, String keyGroup) {
        String sql = "SELECT ID, KEY_NAME, KEY_VALUE, KEY_GROUP FROM LOOKUP WHERE KEY_GROUP = ? LIMIT "+begin+", "+(begin+limit)+"";
        List rows = jdbcTemplate.queryForList(sql, new LookupRowMapper(), new Object[]{keyGroup});
        return rows;
    }
}
